import Database from 'better-sqlite3';
import path from 'path';
import fs from 'fs';

const DB_PATH = path.join(process.cwd(), 'data', 'delivery-management.db');

// 设备接口定义
export interface Device {
  id?: number;
  device_id: number;
  name: string;
  classification_name: string;
  classification_code: string;
  specialty: string;
  naming_table_id: string;
  position_table_id: string;
  base_params_table_id: string;
  tech_spec_table_id: string;
  sequence_number: string;
  remarks: string;
  created_at?: string;
  updated_at?: string;
}

// 参数接口定义
export interface Parameter {
  id?: number;
  parameter_name: string;
  parameter_value: string;
  data_type?: string;
  measurement_unit?: string;
  is_required?: boolean;
  data_source?: string;
  sequence?: string;
  example_description?: string;
  measurement_class?: string;
  created_at?: string;
  updated_at?: string;
}

export class IndependentTablesDatabase {
  private db: Database.Database;

  constructor() {
    // 确保数据目录存在
    const dataDir = path.dirname(DB_PATH);
    if (!fs.existsSync(dataDir)) {
      fs.mkdirSync(dataDir, { recursive: true });
    }

    this.db = new Database(DB_PATH);
    this.db.pragma('journal_mode = WAL');
    this.initializeDatabase();
  }

  // 初始化数据库 - 创建基础表结构
  private initializeDatabase() {
    // 创建设备表
    this.db.exec(`
      CREATE TABLE IF NOT EXISTS devices (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        device_id INTEGER UNIQUE NOT NULL,
        name TEXT NOT NULL,
        classification_name TEXT NOT NULL,
        classification_code TEXT NOT NULL,
        specialty TEXT NOT NULL,
        naming_table_id TEXT,
        position_table_id TEXT,
        base_params_table_id TEXT,
        tech_spec_table_id TEXT,
        sequence_number TEXT,
        remarks TEXT,
        created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
      )
    `);

    // 创建设备表索引
    this.db.exec(`
      CREATE INDEX IF NOT EXISTS idx_devices_device_id ON devices(device_id);
      CREATE INDEX IF NOT EXISTS idx_devices_specialty ON devices(specialty);
    `);
  }

  // 为设备创建独立的属性表
  createDeviceParameterTables(deviceId: number): void {
    const tables = [
      {
        name: `naming_parameters_device_${deviceId}`,
        fields: `
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          sequence TEXT,
          parameter_name TEXT NOT NULL,
          parameter_value TEXT,
          data_type TEXT,
          example_description TEXT,
          measurement_class TEXT,
          data_source TEXT,
          is_required BOOLEAN DEFAULT 0,
          created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
          updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
        `
      },
      {
        name: `position_parameters_device_${deviceId}`,
        fields: `
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          sequence TEXT,
          parameter_name TEXT NOT NULL,
          parameter_value TEXT,
          data_type TEXT,
          example_description TEXT,
          measurement_class TEXT,
          data_source TEXT,
          is_required BOOLEAN DEFAULT 0,
          created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
          updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
        `
      },
      {
        name: `base_parameters_device_${deviceId}`,
        fields: `
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          sequence TEXT,
          parameter_name TEXT NOT NULL,
          parameter_value TEXT,
          data_type TEXT,
          measurement_unit TEXT,
          example_description TEXT,
          measurement_class TEXT,
          data_source TEXT,
          is_required BOOLEAN DEFAULT 0,
          created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
          updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
        `
      }
    ];

    for (const table of tables) {
      this.db.exec(`CREATE TABLE IF NOT EXISTS ${table.name} (${table.fields})`);
      
      // 创建索引
      this.db.exec(`
        CREATE INDEX IF NOT EXISTS idx_${table.name}_parameter_name 
        ON ${table.name}(parameter_name);
      `);
    }
  }

  // 插入设备并创建对应的属性表
  insertDevice(device: Omit<Device, 'id' | 'created_at' | 'updated_at'>): Device {
    const now = new Date().toISOString();
    
    const insertStmt = this.db.prepare(`
      INSERT INTO devices (
        device_id, name, classification_name, classification_code, specialty,
        naming_table_id, position_table_id, base_params_table_id, tech_spec_table_id,
        sequence_number, remarks, created_at, updated_at
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `);

    const result = insertStmt.run(
      device.device_id,
      device.name,
      device.classification_name,
      device.classification_code,
      device.specialty,
      device.naming_table_id,
      device.position_table_id,
      device.base_params_table_id,
      device.tech_spec_table_id,
      device.sequence_number,
      device.remarks,
      now,
      now
    );

    // 为新设备创建独立的属性表
    this.createDeviceParameterTables(device.device_id);

    return {
      id: result.lastInsertRowid as number,
      created_at: now,
      updated_at: now,
      ...device
    };
  }

  // 获取设备的属性表名
  getDeviceTableNames(deviceId: number) {
    return {
      naming: `naming_parameters_device_${deviceId}`,
      position: `position_parameters_device_${deviceId}`,
      base: `base_parameters_device_${deviceId}`
    };
  }

  // 向设备的命名参数表插入数据
  insertNamingParameter(deviceId: number, parameter: Omit<Parameter, 'id' | 'created_at' | 'updated_at'>): Parameter {
    const tableName = `naming_parameters_device_${deviceId}`;
    const now = new Date().toISOString();
    
    const insertStmt = this.db.prepare(`
      INSERT INTO ${tableName} (
        sequence, parameter_name, parameter_value, data_type, example_description,
        measurement_class, data_source, is_required, created_at, updated_at
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `);

    const result = insertStmt.run(
      parameter.sequence || '',
      parameter.parameter_name,
      parameter.parameter_value || '',
      parameter.data_type || '',
      parameter.example_description || '',
      parameter.measurement_class || '',
      parameter.data_source || '',
      parameter.is_required ? 1 : 0,
      now,
      now
    );

    return {
      id: result.lastInsertRowid as number,
      created_at: now,
      updated_at: now,
      ...parameter
    };
  }

  // 向设备的位置参数表插入数据
  insertPositionParameter(deviceId: number, parameter: Omit<Parameter, 'id' | 'created_at' | 'updated_at'>): Parameter {
    const tableName = `position_parameters_device_${deviceId}`;
    const now = new Date().toISOString();
    
    const insertStmt = this.db.prepare(`
      INSERT INTO ${tableName} (
        sequence, parameter_name, parameter_value, data_type, example_description,
        measurement_class, data_source, is_required, created_at, updated_at
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `);

    const result = insertStmt.run(
      parameter.sequence || '',
      parameter.parameter_name,
      parameter.parameter_value || '',
      parameter.data_type || '',
      parameter.example_description || '',
      parameter.measurement_class || '',
      parameter.data_source || '',
      parameter.is_required ? 1 : 0,
      now,
      now
    );

    return {
      id: result.lastInsertRowid as number,
      created_at: now,
      updated_at: now,
      ...parameter
    };
  }

  // 向设备的基础参数表插入数据
  insertBaseParameter(deviceId: number, parameter: Omit<Parameter, 'id' | 'created_at' | 'updated_at'>): Parameter {
    const tableName = `base_parameters_device_${deviceId}`;
    const now = new Date().toISOString();
    
    const insertStmt = this.db.prepare(`
      INSERT INTO ${tableName} (
        sequence, parameter_name, parameter_value, data_type, measurement_unit,
        example_description, measurement_class, data_source, is_required, created_at, updated_at
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `);

    const result = insertStmt.run(
      parameter.sequence || '',
      parameter.parameter_name,
      parameter.parameter_value || '',
      parameter.data_type || '',
      parameter.measurement_unit || '',
      parameter.example_description || '',
      parameter.measurement_class || '',
      parameter.data_source || '',
      parameter.is_required ? 1 : 0,
      now,
      now
    );

    return {
      id: result.lastInsertRowid as number,
      created_at: now,
      updated_at: now,
      ...parameter
    };
  }

  // 获取设备的命名参数
  getDeviceNamingParameters(deviceId: number): Parameter[] {
    const tableName = `naming_parameters_device_${deviceId}`;
    
    try {
      const stmt = this.db.prepare(`SELECT * FROM ${tableName} ORDER BY sequence, id`);
      return stmt.all() as Parameter[];
    } catch (error) {
      // 如果表不存在，返回空数组
      return [];
    }
  }

  // 获取设备的位置参数
  getDevicePositionParameters(deviceId: number): Parameter[] {
    const tableName = `position_parameters_device_${deviceId}`;
    
    try {
      const stmt = this.db.prepare(`SELECT * FROM ${tableName} ORDER BY sequence, id`);
      return stmt.all() as Parameter[];
    } catch (error) {
      // 如果表不存在，返回空数组
      return [];
    }
  }

  // 获取设备的基础参数
  getDeviceBaseParameters(deviceId: number): Parameter[] {
    const tableName = `base_parameters_device_${deviceId}`;
    
    try {
      const stmt = this.db.prepare(`SELECT * FROM ${tableName} ORDER BY sequence, id`);
      return stmt.all() as Parameter[];
    } catch (error) {
      // 如果表不存在，返回空数组
      return [];
    }
  }

  // 获取所有设备
  getAllDevices(): Device[] {
    const stmt = this.db.prepare('SELECT * FROM devices ORDER BY device_id');
    return stmt.all() as Device[];
  }

  // 根据设备ID获取设备
  getDeviceById(deviceId: number): Device | null {
    const stmt = this.db.prepare('SELECT * FROM devices WHERE device_id = ?');
    return stmt.get(deviceId) as Device | null;
  }

  // 关闭数据库连接
  close(): void {
    this.db.close();
  }
}
